-- 创建新用户
CREATE USER lcqh WITH PASSWORD '479368';
DROP DATABASE IF EXISTS blog_cloud;
-- 创建数据库
CREATE DATABASE blog_cloud WITH OWNER = lcqh;
-- 授权
GRANT ALL PRIVILEGES ON DATABASE blog_cloud TO lcqh;
-- 在 psql 命令行中使用
\c blog_cloud;

-- ----------------------------
-- 创建租户表 (Tenants)
-- 在面向租户的 RBAC 的系统中，每个租户都有自己的数据隔离机制，即每个租户的数据与数据之间是隔离的，互不干扰。
-- 在该系统中，系统管理员直接管理所有租户，并分配权限给每个租户。租户的创建、修改、删除等操作，由系统管理员完成，而普通用户则由租户管理员完成。
-- ----------------------------
-- 创建状态枚举类型
CREATE TYPE tenants_status AS ENUM ('待激活', '正常', '已停用');
CREATE TABLE tenants
(
    tenant_id       BIGINT PRIMARY KEY,         -- 相当于一个公司ID
    tenant_name     VARCHAR(50)    NOT NULL,    -- 公司名称
    contact_user_id BIGINT         NOT NULL,    -- 公司联系人用户编号
    contact_name    VARCHAR(50)    NOT NULL,    -- 公司联系人姓名';
    contact_mobile  VARCHAR(20)    NOT NULL,    -- 公司联系人手机号';
    website         VARCHAR(255)   NULL,        -- 公司官网';
    package_id      BIGINT         NOT NULL,    -- 公司购买的套餐编号';
    expire_time     TIMESTAMP      NOT NULL,    -- 公司购买的套餐过期时间';
    account_count   INT            NOT NULL,    -- 公司购总人数';
    status          tenants_status NOT NULL DEFAULT '待激活',
    is_deleted      BOOLEAN        NOT NULL DEFAULT FALSE,
    creator         VARCHAR(50)    NOT NULL,
    create_time     TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updater         VARCHAR(50),
    update_time     TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uniq_tenant_name UNIQUE (tenant_name)
);
-- 添加表/字段注释
COMMENT ON COLUMN tenants.tenant_id IS '租户编号';
COMMENT ON COLUMN tenants.tenant_name IS '租户名称';
COMMENT ON COLUMN tenants.contact_user_id IS '联系人用户编号';
COMMENT ON COLUMN tenants.contact_name IS '联系人姓名';
COMMENT ON COLUMN tenants.contact_mobile IS '联系人手机号';
COMMENT ON COLUMN tenants.website IS '租户官网';
COMMENT ON COLUMN tenants.package_id IS '租户购买的套餐编号';
COMMENT ON COLUMN tenants.expire_time IS '租户套餐过期时间';
COMMENT ON COLUMN tenants.account_count IS '租户旗下账号数量';
COMMENT ON COLUMN tenants.status IS '租户状态';
COMMENT ON COLUMN tenants.is_deleted IS '是否删除';
COMMENT ON COLUMN tenants.creator IS '创建者';
COMMENT ON COLUMN tenants.create_time IS '创建时间';
COMMENT ON COLUMN tenants.updater IS '更新者';
COMMENT ON COLUMN tenants.update_time IS '更新时间';
COMMENT ON TABLE tenants IS '租户信息表';
-- 创建索引（优化查询性能）
CREATE INDEX idx_tenants_last_name ON tenants (tenant_id);
-- 插入租户
INSERT INTO tenants
    (tenant_id, tenant_name, contact_user_id, contact_name, contact_mobile, website, package_id, expire_time, account_count, status, is_deleted, creator, create_time, updater, update_time)
VALUES
    (1, '龙茶清欢有限责任公司', 110, '芋艿', '17321315478', 'www.iocoder.cn', 0, '2099-02-19 17:14:16', 9999, '1', '2021-01-05 17:03:47', '1', '2023-11-06 11:41:41', '0');


-- ----------------------------
-- 创建用户表 (Users)
-- ----------------------------
-- 创建状态枚举类型
CREATE TYPE user_status AS ENUM ('待激活', '启用中', '已停用');
CREATE TABLE users
(
    user_id       BIGINT PRIMARY KEY,
    role_id       BIGINT       NOT NULL REFERENCES roles (role_id) ON DELETE CASCADE,
    tenant_id     BIGINT       NOT NULL REFERENCES tenants (tenant_id) ON DELETE CASCADE,
    username      VARCHAR(50)  NOT NULL,
    password_hash TEXT         NOT NULL,
    email         VARCHAR(100) NOT NULL,
    phone         VARCHAR(15)  NOT NULL,
    age           INT,
    gender        VARCHAR(10),
    birthday      DATE,
    status        user_status  NOT NULL DEFAULT '待激活',
    is_deleted    BOOLEAN      NOT NULL DEFAULT FALSE,
    creator       VARCHAR(50)  NOT NULL,
    create_time   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updater       VARCHAR(50),
    update_time   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uniq_tenant_username UNIQUE (tenant_id, username),
    CONSTRAINT uniq_tenant_email UNIQUE (tenant_id, email)
);
-- 添加表/字段注释
COMMENT ON COLUMN users.user_id IS '用户编号';
COMMENT ON COLUMN users.role_id IS '角色编号';
COMMENT ON COLUMN users.tenant_id IS '租户编号';
COMMENT ON COLUMN users.username IS '用户名';
COMMENT ON COLUMN users.password_hash IS '密码哈希值';
COMMENT ON COLUMN users.email IS '邮箱';
COMMENT ON COLUMN users.phone IS '手机号码';
COMMENT ON COLUMN users.status IS '用户状态';
COMMENT ON COLUMN users.is_deleted IS '是否删除';
COMMENT ON COLUMN users.creator IS '创建者';
COMMENT ON COLUMN users.create_time IS '创建时间';
COMMENT ON COLUMN users.updater IS '更新者';
COMMENT ON COLUMN users.update_time IS '更新时间';
COMMENT ON TABLE users IS '用户信息表';
-- 创建索引（优化查询性能）
CREATE INDEX idx_users_tenant ON users (tenant_id);

-- ----------------------------
-- 创建角色表 (Roles)
-- ----------------------------
CREATE TYPE roles_status AS ENUM ('待激活', '启用中', '已停用');
CREATE TABLE roles
(
    role_id     BIGINT PRIMARY KEY,
    tenant_id   BIGINT       NOT NULL REFERENCES tenants (tenant_id) ON DELETE CASCADE,
    role_name   VARCHAR(50)  NOT NULL,
    description TEXT,
    status      roles_status NOT NULL DEFAULT '待激活',
    is_deleted  BOOLEAN      NOT NULL DEFAULT FALSE,
    creator     VARCHAR(50)  NOT NULL,
    create_time TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updater     VARCHAR(50),
    update_time TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uniq_tenant_role UNIQUE (tenant_id, role_name)
);
-- 添加表/字段注释
COMMENT ON COLUMN roles.role_id IS '角色编号';
COMMENT ON COLUMN roles.tenant_id IS '租户编号';
COMMENT ON COLUMN roles.role_name IS '角色名称';
COMMENT ON COLUMN roles.description IS '角色描述';
COMMENT ON COLUMN users.status IS '角色状态';
COMMENT ON COLUMN users.is_deleted IS '是否删除';
COMMENT ON COLUMN users.creator IS '创建者';
COMMENT ON COLUMN users.create_time IS '创建时间';
COMMENT ON COLUMN users.updater IS '更新者';
COMMENT ON COLUMN users.update_time IS '更新时间';
COMMENT ON TABLE roles IS '角色定义表';
-- 创建索引（优化查询性能）
CREATE INDEX idx_roles_tenant ON roles (tenant_id);

-- ----------------------------
-- 创建权限表 (Permissions)
-- ----------------------------
CREATE TABLE permissions
(
    permission_id   BIGINT PRIMARY KEY,
    tenant_id       BIGINT       NOT NULL REFERENCES tenants (tenant_id) ON DELETE CASCADE,
    permission_code VARCHAR(50)  NOT NULL,
    description     TEXT,
    resource_type   VARCHAR(50)  NOT NULL,
    action          VARCHAR(20)  NOT NULL,
    status          roles_status NOT NULL DEFAULT '待激活',
    is_deleted      BOOLEAN      NOT NULL DEFAULT FALSE,
    creator         VARCHAR(50)  NOT NULL,
    create_time     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updater         VARCHAR(50),
    update_time     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uniq_tenant_perm UNIQUE (tenant_id, permission_code)
);
-- 添加表/字段注释
COMMENT ON COLUMN permissions.permission_id IS '权限编号';
COMMENT ON COLUMN permissions.tenant_id IS '租户编号';
COMMENT ON COLUMN permissions.permission_code IS '权限编码（如user:create）';
COMMENT ON COLUMN permissions.description IS '权限描述';
COMMENT ON COLUMN permissions.resource_type IS '资源类型（如：user）';
COMMENT ON COLUMN permissions.action IS '操作类型（如：create）';
COMMENT ON COLUMN permissions.status IS '权限状态';
COMMENT ON COLUMN permissions.is_deleted IS '是否删除';
COMMENT ON COLUMN permissions.creator IS '创建者';
COMMENT ON COLUMN permissions.create_time IS '创建时间';
COMMENT ON COLUMN permissions.updater IS '更新者';
COMMENT ON COLUMN permissions.update_time IS '更新时间';
COMMENT ON TABLE permissions IS '权限定义表';
-- 创建索引（优化查询性能）
CREATE INDEX idx_permissions_tenant ON permissions (tenant_id);

-- ----------------------------
-- 用户角色关联表 (User_Roles)
-- ----------------------------
CREATE TABLE user_roles
(
    user_id    BIGINT    NOT NULL REFERENCES users (user_id) ON DELETE CASCADE,
    role_id    BIGINT    NOT NULL REFERENCES roles (role_id) ON DELETE CASCADE,
    tenant_id  BIGINT    NOT NULL REFERENCES tenants (tenant_id) ON DELETE CASCADE,
    start_time TIMESTAMP NOT NULL DEFAULT NOW(),
    end_time   TIMESTAMP,
    PRIMARY KEY (user_id, role_id, tenant_id),
    CONSTRAINT chk_end_time CHECK (end_time IS NULL OR end_time > start_time)
);
-- 添加表/字段注释
COMMENT ON TABLE user_roles IS '用户角色关联表';
-- 创建索引（优化查询性能）
CREATE INDEX idx_user_roles_tenant ON user_roles (tenant_id);
CREATE INDEX idx_user_roles_time ON user_roles (start_time, end_time);

-- ----------------------------
-- 角色权限关联表 (Role_Permissions)
-- ----------------------------
CREATE TABLE role_permissions
(
    role_id       BIGINT NOT NULL REFERENCES roles (role_id) ON DELETE CASCADE,
    permission_id BIGINT NOT NULL REFERENCES permissions (permission_id) ON DELETE CASCADE,
    tenant_id     BIGINT NOT NULL REFERENCES tenants (tenant_id) ON DELETE CASCADE,
    PRIMARY KEY (role_id, permission_id, tenant_id)
);
-- 添加表/字段注释
COMMENT ON TABLE role_permissions IS '角色权限关联表';
-- 创建索引（优化查询性能）
CREATE INDEX idx_role_perms_tenant ON role_permissions (tenant_id);

-- ----------------------------
-- 审计日志表 (Audit_Logs)
-- ----------------------------
CREATE TABLE audit_logs
(
    log_id         BIGINT PRIMARY KEY,
    tenant_id      BIGINT      NOT NULL REFERENCES tenants (tenant_id) ON DELETE CASCADE,
    operation_type VARCHAR(20) NOT NULL CHECK (operation_type IN ('CREATE', 'UPDATE', 'DELETE')),
    operator_id    BIGINT      NOT NULL REFERENCES users (user_id) ON DELETE SET NULL,
    target_type    VARCHAR(50) NOT NULL,
    target_id      BIGINT,
    old_data       JSONB,
    new_data       JSONB,
    created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);
COMMENT ON TABLE audit_logs IS '审计日志表';

-- 创建默认分区（按年分区）
CREATE TABLE audit_logs_2023 PARTITION OF audit_logs
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE INDEX idx_audit_tenant_op ON audit_logs (tenant_id, operation_type);
CREATE INDEX idx_audit_time ON audit_logs (created_at);

-- ----------------------------
-- 部门表 (System_Dept)
-- ----------------------------
CREATE TABLE system_dept
(
    id             int8        NOT NULL,
    name           varchar(30) NOT NULL DEFAULT '',
    parent_id      int8        NOT NULL DEFAULT 0,
    sort           int4        NOT NULL DEFAULT 0,
    leader_user_id int8        NULL     DEFAULT NULL,
    phone          varchar(11) NULL     DEFAULT NULL,
    email          varchar(50) NULL     DEFAULT NULL,
    status         int2        NOT NULL,
    creator        varchar(64) NULL     DEFAULT '',
    create_time    timestamp   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updater        varchar(64) NULL     DEFAULT '',
    update_time    timestamp   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted        int2        NOT NULL DEFAULT 0,
    tenant_id      int8        NOT NULL DEFAULT 0
);

ALTER TABLE system_dept
    ADD CONSTRAINT pk_system_dept PRIMARY KEY (id);

COMMENT ON COLUMN system_dept.id IS '部门id';
COMMENT ON COLUMN system_dept.name IS '部门名称';
COMMENT ON COLUMN system_dept.parent_id IS '父部门id';
COMMENT ON COLUMN system_dept.sort IS '显示顺序';
COMMENT ON COLUMN system_dept.leader_user_id IS '负责人';
COMMENT ON COLUMN system_dept.phone IS '联系电话';
COMMENT ON COLUMN system_dept.email IS '邮箱';
COMMENT ON COLUMN system_dept.status IS '部门状态（0正常 1停用）';
COMMENT ON COLUMN system_dept.creator IS '创建者';
COMMENT ON COLUMN system_dept.create_time IS '创建时间';
COMMENT ON COLUMN system_dept.updater IS '更新者';
COMMENT ON COLUMN system_dept.update_time IS '更新时间';
COMMENT ON COLUMN system_dept.deleted IS '是否删除';
COMMENT ON COLUMN system_dept.tenant_id IS '租户编号';
COMMENT ON TABLE system_dept IS '部门表';

-- ----------------------------
-- 字典数据表 (System_Data_Dict)
-- ----------------------------
CREATE TABLE system_data_dict
(
    id          int8         NOT NULL,
    sort        int4         NOT NULL DEFAULT 0,
    label       varchar(100) NOT NULL DEFAULT '',
    value       varchar(100) NOT NULL DEFAULT '',
    dict_type   varchar(100) NOT NULL DEFAULT '',
    status      int2         NOT NULL DEFAULT 0,
    color_type  varchar(100) NULL     DEFAULT '',
    css_class   varchar(100) NULL     DEFAULT '',
    remark      varchar(500) NULL     DEFAULT NULL,
    creator     varchar(64)  NULL     DEFAULT '',
    create_time timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updater     varchar(64)  NULL     DEFAULT '',
    update_time timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted     int2         NOT NULL DEFAULT 0
);

COMMENT ON COLUMN system_data_dict.id IS '字典编码';
COMMENT ON COLUMN system_data_dict.sort IS '字典排序';
COMMENT ON COLUMN system_data_dict.label IS '字典标签';
COMMENT ON COLUMN system_data_dict.value IS '字典键值';
COMMENT ON COLUMN system_data_dict.dict_type IS '字典类型';
COMMENT ON COLUMN system_data_dict.status IS '状态（0正常 1停用）';
COMMENT ON COLUMN system_data_dict.color_type IS '颜色类型';
COMMENT ON COLUMN system_data_dict.css_class IS 'css 样式';
COMMENT ON COLUMN system_data_dict.remark IS '备注';
COMMENT ON COLUMN system_data_dict.creator IS '创建者';
COMMENT ON COLUMN system_data_dict.create_time IS '创建时间';
COMMENT ON COLUMN system_data_dict.updater IS '更新者';
COMMENT ON COLUMN system_data_dict.update_time IS '更新时间';
COMMENT ON COLUMN system_data_dict.deleted IS '是否删除';
COMMENT ON TABLE system_data_dict IS '字典数据表';


-- =============================================
-- 创建视图（简化复杂查询）
-- =============================================
CREATE OR REPLACE VIEW active_employees AS
SELECT e.employee_id,
       CONCAT(e.first_name, ' ', e.last_name) AS full_name,
       d.department_name,
       e.hire_date
FROM employees e
         LEFT JOIN departments d
                   ON e.department_id = d.department_id
WHERE e.hire_date >= '2023-01-01';

-- ----------------------------
-- 审计日志触发器函数
-- ----------------------------
CREATE OR REPLACE FUNCTION log_audit_event()
    RETURNS TRIGGER AS
$$
DECLARE
    v_old_data JSONB;
    v_new_data JSONB;
BEGIN
    IF (TG_OP = 'DELETE') THEN
        v_old_data := to_jsonb(OLD);
        INSERT INTO audit_logs (tenant_id, operation_type, operator_id, target_type, target_id, old_data)
        VALUES (OLD.tenant_id, 'DELETE', current_user, TG_TABLE_NAME, OLD.user_id, v_old_data);
        RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
        v_old_data := to_jsonb(OLD);
        v_new_data := to_jsonb(NEW);
        INSERT INTO audit_logs (tenant_id, operation_type, operator_id, target_type, target_id, old_data, new_data)
        VALUES (NEW.tenant_id, 'UPDATE', current_user, TG_TABLE_NAME, NEW.user_id, v_old_data, v_new_data);
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        v_new_data := to_jsonb(NEW);
        INSERT INTO audit_logs (tenant_id, operation_type, operator_id, target_type, target_id, new_data)
        VALUES (NEW.tenant_id, 'CREATE', current_user, TG_TABLE_NAME, NEW.user_id, v_new_data);
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 为用户表创建触发器
CREATE TRIGGER trg_users_audit
    AFTER INSERT OR UPDATE OR DELETE
    ON users
    FOR EACH ROW
EXECUTE FUNCTION log_audit_event();

-- ----------------------------
-- 初始化示例数据
-- ----------------------------


-- 插入用户（密码均为password123）
INSERT INTO users (user_id, tenant_id, username, password_hash, email)
VALUES ('10000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000001', 'admin', 'password123',
        'admin@example.com'),
       ('10000000-0000-0000-0000-000000000002', '00000000-0000-0000-0000-000000000001', 'user1', 'password123',
        'user1@example.com');

-- 插入角色
INSERT INTO roles (role_id, tenant_id, role_name, description)
VALUES ('20000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000001', '超级管理员',
        '系统最高权限角色'),
       ('20000000-0000-0000-0000-000000000002', '00000000-0000-0000-0000-000000000001', '普通用户', '基础访问权限');

-- 插入权限
INSERT INTO permissions (permission_id, tenant_id, permission_code, description, resource_type, action)
VALUES ('30000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000001', 'user:manage', '管理用户权限',
        'user', 'write'),
       ('30000000-0000-0000-0000-000000000002', '00000000-0000-0000-0000-000000000001', 'data:view', '查看业务数据',
        'data', 'read');

-- 分配角色权限
INSERT INTO role_permissions (role_id, permission_id, tenant_id)
VALUES ('20000000-0000-0000-0000-000000000001', '30000000-0000-0000-0000-000000000001',
        '00000000-0000-0000-0000-000000000001'),
       ('20000000-0000-0000-0000-000000000001', '30000000-0000-0000-0000-000000000002',
        '00000000-0000-0000-0000-000000000001'),
       ('20000000-0000-0000-0000-000000000002', '30000000-0000-0000-0000-000000000002',
        '00000000-0000-0000-0000-000000000001');

-- 分配用户角色
INSERT INTO user_roles (user_id, role_id, tenant_id, end_time)
VALUES ('10000000-0000-0000-0000-000000000001', '20000000-0000-0000-0000-000000000001',
        '00000000-0000-0000-0000-000000000001', NULL),
       ('10000000-0000-0000-0000-000000000002', '20000000-0000-0000-0000-000000000002',
        '00000000-0000-0000-0000-000000000001', '2024-12-31 23:59:59+00');